package org.excel;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.SocketException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

/**
 * 导出工具
 */
@SuppressWarnings("all")
public class ExcelExportUtil {

	public static void export(HttpServletResponse response, String fileName, String[][] header, String sheetName, int pageSize, Connection conn, String sql, Object... paras) throws Exception {
		List<Map<String, String>> data = query(conn, sql, paras);
		int size = data.size();
		if (size > pageSize) {
			int pageTotal = size % pageSize == 0 ? size / pageSize : size / pageSize + 1;
			ZipOutputStream zos = new ZipOutputStream(getHttpOutputStream(response, fileName + ".zip"));
			try {
				for (int i = 1; i <= pageTotal; i++) {
					ByteArrayOutputStream bos = new ByteArrayOutputStream();
					writeExcel(sheetName, header, data.subList((i - 1) * pageSize, i == pageTotal ? size - 1 : i * pageSize), bos);
					ZipEntry ze = new ZipEntry(String.format("partition-%d.xls", i));
					zos.putNextEntry(ze);
					zos.write(bos.toByteArray());
					bos.flush();
					bos.close();
					zos.closeEntry();
				}
			} catch (Exception e) {
				throw e;
			} finally {
				zos.close();
			}
		} else {
			writeExcel(sheetName, header, data, getHttpOutputStream(response, fileName + ".xls"));
		}
	}

	public static void export(String dir, String fileName, String[][] header, String sheetName, int pageSize, Connection conn, String sql, Object... paras) throws Exception {
		List<Map<String, String>> data = query(conn, sql, paras);
		int size = data.size();
		if (size > pageSize) {
			int pageTotal = size % pageSize == 0 ? size / pageSize : size / pageSize + 1;
			ZipOutputStream zos = new ZipOutputStream(getFileOutputStream(dir, fileName + ".zip"));
			try {
				for (int i = 1; i <= pageTotal; i++) {
					ByteArrayOutputStream bos = new ByteArrayOutputStream();
					writeExcel(sheetName, header, data.subList((i - 1) * pageSize, i == pageTotal ? size - 1 : i * pageSize), bos);
					ZipEntry ze = new ZipEntry(String.format("partition-%d.xls", i));
					zos.putNextEntry(ze);
					zos.write(bos.toByteArray());
					bos.flush();
					bos.close();
					zos.closeEntry();
				}
			} catch (Exception e) {
				throw e;
			} finally {
				zos.close();
			}
		} else {
			writeExcel(sheetName, header, data, getFileOutputStream(dir, fileName + ".xls"));
		}
	}

	private static OutputStream getHttpOutputStream(HttpServletResponse response, String fileName) throws Exception {
		response.setContentType("application/octet-stream; charset=UTF-8");
		response.setHeader("Content-Disposition", "attachment; filename=\"" + new String(fileName.getBytes("GBK"), "ISO8859_1") + "\"");
		return response.getOutputStream();
	}

	private static OutputStream getFileOutputStream(String dir, String fileName) throws Exception {
		if (!new File(dir).exists() && !new File(dir).mkdirs()) {
			throw new RuntimeException("目录创建失败！");
		}
		return new FileOutputStream(new File(dir, fileName));
	}

	private static List<Map<String, String>> query(Connection conn, String sql, Object... paras) throws SQLException {
		List<Map<String, String>> result = new ArrayList<Map<String, String>>();
		PreparedStatement pst = conn.prepareStatement(sql);
		for (int i = 0; i < paras.length; i++) {
			pst.setObject(i + 1, paras[i]);
		}
		ResultSet rs = pst.executeQuery();
		ResultSetMetaData md = rs.getMetaData();
		int columnCount = md.getColumnCount();
		while (rs.next()) {
			Map<String, String> rowData = new HashMap<String, String>();
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i).toLowerCase(), rs.getString(i));
			}
			result.add(rowData);
		}
		rs.close();
		pst.close();
		return result;
	}

	private static void writeExcel(String sheetName, String[][] headers, Collection<Map<String, String>> dataset, OutputStream out) throws IOException {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(sheetName);
		sheet.setDefaultColumnWidth(15);
		HSSFCellStyle style = workbook.createCellStyle();
		HSSFFont font = workbook.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 9);
		style.setFont(font);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setWrapText(false);// 设置不自动换行
		style.setFillForegroundColor(HSSFColor.YELLOW.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setFont(font);
		HSSFCellStyle style2 = workbook.createCellStyle();
		HSSFFont font2 = workbook.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 9);
		style.setFont(font);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setWrapText(false);// 设置不自动换行
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		// 把字体应用到当前的样式
		style2.setFont(font2);
		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			if (headers[i].length == 3) {
				sheet.setColumnWidth(i, (short) (35.7 * Integer.valueOf(headers[i][2]).intValue()));
			}
			HSSFRichTextString text = new HSSFRichTextString(headers[i][1]);
			cell.setCellValue(text);
		}
		// 遍历集合数据，产生数据行
		Iterator<Map<String, String>> it = dataset.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			Map<String, String> t = it.next();
			for (int i = 0; i < headers.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(style2);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				String value = t.get(headers[i][0].toLowerCase());
				// 判断值的类型后进行强制类型转换
				HSSFRichTextString textValue = new HSSFRichTextString(value == null ? "" : value);
				cell.setCellValue(textValue);
			}
		}
		workbook.write(out);
	}

}